package com.timemanager.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.timemanager.activity.EventSelectorActivity;
import com.timemanager.model.Event;
import com.timemanager.model.EventAndEventTime;
import com.timemanager.model.EventTime;
import com.timemanager.model.TypeElapsedTime;

import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by He ZQ on 2016/3/25.
 * Email: Alexmocer@hotmail.com
 */
public class TimeManagerDB {
    /**
     * 数据库名
     */
    public static final String DB_NAME = "time_manager";

    /**
     * 数据库版本
     */
    public static final int VERSION = 1;

    private static TimeManagerDB timeManagerDB;

    private SQLiteDatabase db;

    /**
     * 将构造方法私有化
     */
    private TimeManagerDB(Context context) {
        TimeManagerOpenHelper dbHelper = new TimeManagerOpenHelper(context,
                DB_NAME, null, VERSION);
        db = dbHelper.getWritableDatabase();
    }

    /**
     * 获取TimeManager实例
     */
    public synchronized static TimeManagerDB getInstance(Context context) {
        if (timeManagerDB == null) {
            timeManagerDB = new TimeManagerDB(context);
        }
        return timeManagerDB;
    }

    /**
     * 将Event实例保存到数据库，返回>0保存成功，-1为保存失败，-2为传入空值
     */
    public long saveEvent(Event event) {
        if (event != null) {
            if (db.query("Event", null, "event_name = ?",
                    new String[]{ event.getName() }, null, null, null).getCount() != 0) {
                return -1;
            } else {
                ContentValues values = new ContentValues();
                values.put("event_type", event.getType());
                values.put("event_name", event.getName());
                return db.insert("Event", null, values);
            }
        } else {
            return -2;
        }
    }
    /**
     * 根据传入ID删除数据库中的event删除成功返回true
     */
    public boolean deleteEvent(int eventId) {
        if (db.query("Event_Time", null, "event_id = ?",
                new String[]{ ""+eventId }, null, null, null).getCount() != 0) {
            return false;
        } else {
            db.delete("Event", "id = ?", new String[]{ String.valueOf(eventId) });
            return true;
        }
    }

    /**
     * 修改数据库中的event
     */
    public int updateEvent(Event event) {
        ContentValues values = new ContentValues();
        values.put("event_type", event.getType());
        values.put("event_name", event.getName());
        int result;
        try {
            result = db.update("Event", values, "id = ?", new String[]{String.valueOf(event.getId())});
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    /**
     * 加载相关Type下的Events
     */
    public List<Event> loadEvents(String eventType) {
        if (eventType != null) {
            List<Event> list = new ArrayList<Event>();
            Cursor cursor = db.query("Event", null, "event_type = ?",
                    new String[]{eventType}, null, null, null);
            if (cursor.moveToFirst()) {
                do {
                    Event event = new Event();
                    event.setId(cursor.getInt(cursor.getColumnIndex("id")));
                    event.setType(cursor.getString(cursor.getColumnIndex("event_type")));
                    event.setName(cursor.getString(cursor.getColumnIndex("event_name")));
                    list.add(event);
                } while (cursor.moveToNext());
            }
            if (cursor != null) {
                cursor.close();
            }
            return list;
        } else {
            return null;
        }
    }
    /**
     * 查询数据库根据eventName返回Event实例*/
    public Event searchEventName (String eventName) {
        Event event = new Event();
        if (eventName != null) {
            Cursor cursor = db.query("Event", null, "event_name = ?",
                    new String[]{eventName}, null, null, null);
            cursor.moveToFirst();
            event.setId(cursor.getInt(cursor.getColumnIndex("id")));
            event.setType(cursor.getString(cursor.getColumnIndex("event_type")));
            event.setName(cursor.getString(cursor.getColumnIndex("event_name")));
            if (cursor != null) {
                cursor.close();
            }
            return event;
        }
        return null;
    }

    /**
     * 将EventTime实例保存到数据库，返回>0保存成功，-1为保存失败，-2为传入空值
     */
    public long saveEventTime(EventTime eventTime) {
        if (eventTime != null) {
            ContentValues values = new ContentValues();
            values.put("event_id", eventTime.getEventId());
            if (eventTime.getStartDateTime() != null) {
                values.put("start_datetime", eventTime.getStartDateTime());
            }
            values.put("elapsed_time", eventTime.getElapsedTime());
            return db.insert("Event_Time", null, values);
        } else {
            return -2;
        }
    }

    /**
     * 加载时间范围内的EventAndEventTime列表
     */
    public List<EventAndEventTime> loadEventAndEventTime(String fromDate, String toDate) {
        if (fromDate != null && toDate != null) {
            List<EventAndEventTime> eventAndEventTimes = new ArrayList<EventAndEventTime>();
            Cursor cursor = db.rawQuery("SELECT Event.event_type, Event.event_name, " +
                    "Event_Time.id, Event_Time.elapsed_time, Event_Time.start_datetime FROM Event, Event_Time " +
                    "WHERE Event.id = Event_Time.event_id AND " +
                    "date(Event_Time.start_datetime) BETWEEN ? AND ? ORDER BY Event_Time.start_datetime", new String[] { fromDate, toDate });
            if (cursor.moveToFirst()) {
                do {
                    EventAndEventTime eventAndEventTime = new EventAndEventTime();
                    eventAndEventTime.setId(cursor.getInt(cursor.getColumnIndex("id")));
                    eventAndEventTime.setEventType(cursor.getString(cursor.getColumnIndex("event_type")));
                    eventAndEventTime.setEventName(cursor.getString(cursor.getColumnIndex("event_name")));
                    eventAndEventTime.setElapsedTime(cursor.getInt(cursor.getColumnIndex("elapsed_time")));
                    eventAndEventTime.setStartDateTime(cursor.getString(cursor.getColumnIndex("start_datetime")));
                    eventAndEventTimes.add(eventAndEventTime);
                } while (cursor.moveToNext());
            } if (cursor != null) {
                cursor.close();
            } return eventAndEventTimes;
        } else {
            return null;
        }
    }

    /**
     * 获取日期范围内的event_type与elapsed_time列表供生成饼状图用
     */
    public List<TypeElapsedTime> loadTypeElapsedTime(String fromDate, String toDate) {
        if (fromDate != null && toDate != null) {
            List<TypeElapsedTime> typeElapsedTimes = new ArrayList<TypeElapsedTime>();
            Cursor cursor = db.rawQuery("SELECT Event.event_type,SUM(Event_Time.elapsed_time) AS 'total_elapsed_time'" +
                    "FROM Event, Event_Time WHERE Event.id = Event_Time.event_id " +
                    "AND date(Event_Time.start_datetime) BETWEEN ? AND ? GROUP BY Event.event_type", new String[] { fromDate, toDate });
            if (cursor.moveToFirst()) {
                do {
                    TypeElapsedTime typeElapsedTime = new TypeElapsedTime();
                    typeElapsedTime.setType(cursor.getString(cursor.getColumnIndex("event_type")));
                    typeElapsedTime.setElapsed_time((float) Math.round(cursor.getInt(cursor.getColumnIndex("total_elapsed_time")) / 1000.0));
                    typeElapsedTimes.add(typeElapsedTime);
                } while (cursor.moveToNext());
            } if (cursor != null) {
                cursor.close();
            } return typeElapsedTimes;
        } else {
            return null;
        }
    }
    /**
     * 根据传入ID删除数据库中的EventTime删除成功返回true
     */
    public boolean deleteEventTime(int eventTimeId) {
        db.delete("Event_Time", "id = ?", new String[]{ String.valueOf(eventTimeId) });
        return true;
    }
}
